SELECT and INSERT

SELECT and INSERT#

MySQL provides us the facility to insert several rows from another table into an existing table using a combination of select and insert statements. In fact, we can also create a table on the fly and fill it up with rows from another table.

Syntax to Insert in an Existing Table#

INSERT INTO table1 (col1, col2)

SELECT col3, col4

FROM table2;

Syntax to Insert in a New Table#

CREATE TABLE newTable (col1 <datatype>, <col2>)

SELECT col3, col4

FROM table2;

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/35lesson.sh and wait for the MySQL prompt to start-up.

1. We can populate data into a table from another table using INSERT and SELECT in a single query. Say, we want to create a table of all the second names of actors. We’ll create a table as follows:

   CREATE TABLE Names (name VARCHAR(20),
                    PRIMARY KEY(name));
   

   Now we can insert using INSERT and SELECT in a single statement as follows:

   INSERT INTO Names(name) 
   SELECT SecondName FROM Actors;
   

2. Note that in creating the table Names we have set the only column as the primary key, however, we aren’t guaranteed that the values being selected from the Actors table will not contain duplicates. Let’s try to insert an existing row into the table:

As you can see, trying to add a duplicate rows result in an error. MySQL provides a way to bypass this error and continue execution using the IGNORE clause. It doesn’t mean a duplicate row is added to the table, rather it means that MySQL issues a warning instead of issuing an error and aborting.

INSERT IGNORE INTO Names(name) 
SELECT SecondName 
FROM Actors WHERE Id = 1;

Note that the query finishes successfully but informs the user about the duplicate row. Both the duplicate and warning counts read one.

3. In one of the previous examples we created the table first and then inserted data into the table. We can do both tasks in one shot. Consider the following query:

   CREATE TABLE MyTempTable SELECT * FROM Actors;
   

As you can see we get a copy of the data with the above query but if you pay attention to the describe statement, you’ll notice that the table we create doesn’t inherit the primary key constraints. In fact, creating and copying data as above will not create foreign or primary key constraints on the copy table.

4. All the modifiers that can be used in a stand-alone create table statement can also be used in a combined create and populate table statement.

   CREATE TABLE NamesWithDoBs ( 
   Id INT AUTO_INCREMENT,
   Name VARCHAR(20) NOT NULL DEFAULT "unknown",  
   DoB DATE,  
   PRIMARY KEY(Id), KEY(Name), KEY(DoB))  SELECT FirstName, DoB FROM Actors;
   

5. We can also create a copy of an existing table without the data using the LIKE operator. For instance:

   CREATE TABLE CopyOfActors LIKE Actors;
   

As you can see, the copy table doesn’t contain any data, but its structure is exactly the same as the source table. The primary keys and any indexes defined on the source table are also defined on the copy table.